#include "sqlite3_driver.hpp"
#include <algorithm>
#include <iostream>
#include <list>
#include <numeric>
#include "SQLiteCpp/SQLiteCpp.h"
#include "utils/path_utils.hpp"

using namespace SQLite;

static const UpgradeInfo &GetDatabaseUpgradeInfo (int32_t current_version)
{
    static std::list<UpgradeInfo> infos {
            UpgradeInfo {.version_from = MAKE_VERSION_CODE (1, 0, 0),
                         .version_to   = MAKE_VERSION_CODE (2, 0, 0),
                         .statements =
                                 {
                                         "alter table tags add column tag_level int default 0;",  // 2.0.0 版本增加了标签级别的属性
                                 }},
            UpgradeInfo {.version_from = MAKE_VERSION_CODE (2, 0, 0),
                         .version_to   = MAKE_VERSION_CODE (3, 0, 0),
                         .statements = {"create table work_tasks(task_id integer primary key autoincrement, task_desc text not null, create_date datetime not null, "
                                        "issue_id int, act_id int, status int default 1, tags varchar(64) default '', display_color_rgba integer not "
                                        "null, last_time_entry int not null default -1, foreign key (issue_id) references redmine_issues(issue_id) on update cascade on delete set null, "
                                        "foreign key (act_id) references redmine_activities(act_id) on update cascade on delete set null);",
                                        "create table time_entries(entry_id integer primary key autoincrement, task_id int, begin_time datetime not null, end_time datetime, "
                                        "duration int, exported int not null default 0, foreign key (task_id) references work_tasks(task_id) on update cascade on delete set null);"}},
            UpgradeInfo {.version_from = MAKE_VERSION_CODE (3, 0, 0),
                         .version_to = MAKE_VERSION_CODE (4, 0, 0), 
                         .statements = {"alter table tags add column tag_disabled int default 0;"}}, // 4.0.0 版本添加禁用标签功能
            UpgradeInfo {.version_from = MAKE_VERSION_CODE (4, 0, 0),
                         .version_to = MAKE_VERSION_CODE (5, 0, 0), 
                         .statements = {"alter table work_items add column priority int check(priority>=0 and priority<=9) default 0;"}}, // 5.0.0 版本添加事项优先级，用于自定义排序
    };

    auto it = std::find_if (begin (infos), end (infos), [current_version] (auto const &x) { return x.version_from == current_version; });
    assert (it != end (infos));
    return *it;
}

struct Sqlite3DriverData {
    Database db {"db.sqlite3"_DP, OPEN_READWRITE | OPEN_CREATE | OPEN_FULLMUTEX};
};

template<typename T>
inline T GetValue(Column c, T def);

#define DECL_IMPL(type, function) \
    template<> inline type GetValue(Column c, type def) { return c.isNull() ? def : (type)c.function(); }

DECL_IMPL(int8_t, getInt)
DECL_IMPL(int16_t, getInt)
DECL_IMPL(int32_t, getInt)
DECL_IMPL(int64_t, getInt64)
DECL_IMPL(uint8_t, getUInt)
DECL_IMPL(uint16_t, getUInt)
DECL_IMPL(uint32_t, getUInt)
DECL_IMPL(float, getDouble)
DECL_IMPL(double, getDouble)
DECL_IMPL(std::string, getString)

#undef DECL_IMPL

Sqlite3Driver::Sqlite3Driver () : d {new Sqlite3DriverData {}} {}

Sqlite3Driver::~Sqlite3Driver ()
{
    delete d;
}

void Sqlite3Driver::InitTables () const
{
    // 这里创建的表结构需要匹配 1.0.0 版本的数据
    static const char *table_create_queries[] {
            "create table if not exists redmine_activities(act_id int primary key, act_name varchar(64) not null unique);",

            // RedMineActivities
            "create table if not exists redmine_issues(issue_id int primary key, issue_name varchar(128) not null unique, project_name varchar(128) "
            "not null, assigned_to varchar(32), is_closed int default 0);",

            // RedMineIssues
            "create table if not exists tags(tag_id integer primary key autoincrement, tag_name varchar(32) not null unique, tag_color integer not "
            "null);",

            // Tags
            "create table if not exists work_items(work_id integer primary key autoincrement, hour real not null, comment varchar(255) not null, "
            "note text, create_date date not null, act_id int, issue_id int, is_uploaded int default 0, foreign key (act_id) references "
            "redmine_activities(act_id) on update cascade on delete set null, foreign key (issue_id) references redmine_issues(issue_id) on update "
            "cascade on delete set null);",

            // WorkItems
            "create table if not exists work_item_tags(work_id int not null, tag_id int not null, primary key (work_id, tag_id), foreign key "
            "(work_id) references work_items(work_id) on update cascade on delete cascade, foreign key (tag_id) references tags(tag_id) on update "
            "cascade on delete cascade);",

            // ItemTags
            "create table if not exists _version_(version_code int not null);",  // 记录版本的表
            nullptr,
    };

    for (int i = 0; table_create_queries[i] != nullptr; ++i)
    {
        try
        {
            Statement stmt {d->db, table_create_queries[i]};
            stmt.exec ();
        }
        catch (Exception &ex)
        {
            perror (ex.what ());
        }
    }
}

bool Sqlite3Driver::KeepAlive ()
{
    return true;
}

std::vector<Tag> Sqlite3Driver::AllTags () const
{
    std::vector<Tag> tags;

    try
    {
        Statement count {d->db, "select count(*) from tags;"};
        count.executeStep ();
        const int total = count.getColumn (0);
        Statement stmt {d->db, "select * from tags order by tag_disabled asc, tag_level asc, tag_id asc;"};
        if (total > 0)
        {
            tags.reserve (total);
            while (stmt.executeStep ())
            {
                Tag tag {stmt.getColumn ("tag_id").getInt (), stmt.getColumn ("tag_name").getString (), stmt.getColumn ("tag_color").getUInt (),
                         stmt.getColumn ("tag_level").getUInt (), (bool)stmt.getColumn ("tag_disabled").getInt()};
                tags.emplace_back (std::move (tag));
            }
        }
    }
    catch (Exception &)
    {
    }

    return tags;
}

Tag Sqlite3Driver::NewTag (const std::string &tag_name, uint32_t color, uint32_t level) const
{
    Tag tag {0, tag_name, color, level};

    try
    {
        Statement stmt (d->db, "insert into tags(tag_name, tag_color, tag_level, tag_disabled) values(?,?,?,?) returning tag_id;");
        stmt.bind (1, tag_name);
        stmt.bind (2, color);
        stmt.bind (3, level);
        stmt.bind (4, 0); // default enabled
        if (stmt.executeStep ())
        {
            tag.tag_id = stmt.getColumn (0);
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return tag;
}

bool Sqlite3Driver::DeleteTag (int tag_id) const
{
    try
    {
        Statement stmt (d->db, "delete from tags where tag_id=?;");
        stmt.bind (1, tag_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return false;
}

bool Sqlite3Driver::UpdateTag (Tag &tag) const
{
    try
    {
        Statement stmt (d->db, "update tags set tag_name=?,tag_color=?,tag_level=?,tag_disabled=? where tag_id=?");
        stmt.bind (1, tag.tag_name);
        stmt.bind (2, tag.tag_color);
        stmt.bind (3, tag.tag_level);
        stmt.bind (4, tag.tag_disabled ? 1 : 0);
        stmt.bind (5, tag.tag_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return false;
}

std::vector<RedMineActivity> Sqlite3Driver::AllRedMineActivities () const
{
    std::vector<RedMineActivity> acts;

    try
    {
        Statement count {d->db, "select count(*) from redmine_activities;"};
        count.executeStep ();
        const int total = count.getColumn (0);
        Statement stmt {d->db, "select * from redmine_activities;"};
        if (total > 0)
        {
            acts.reserve (total);
            while (stmt.executeStep ())
            {
                RedMineActivity act {stmt.getColumn ("act_id").getInt (), stmt.getColumn ("act_name").getString ()};
                acts.emplace_back (std::move (act));
            }
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return acts;
}

bool Sqlite3Driver::DeleteRedMineActivity (int act_id) const
{
    try
    {
        Statement stmt (d->db, "delete from redmine_activities where act_id=?;");
        stmt.bind (1, act_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return false;
}

bool Sqlite3Driver::UpdateRedMineActivity (RedMineActivity &tag) const
{
    try
    {
        Statement stmt {d->db, "insert or replace into redmine_activities(act_id, act_name) values (?,?);"};
        stmt.bind (1, tag.act_id);
        stmt.bind (2, tag.act_name);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

std::vector<RedMineIssue> Sqlite3Driver::AllRedMineIssues () const
{
    std::vector<RedMineIssue> issues;

    try
    {
        Statement count {d->db, "select count(*) from redmine_issues;"};
        count.executeStep ();
        const int total = count.getColumn (0);
        Statement stmt {d->db, "select * from redmine_issues order by is_closed asc;"};
        if (total > 0)
        {
            issues.reserve (total);
            while (stmt.executeStep ())
            {
                RedMineIssue issue {stmt.getColumn ("issue_id").getInt (), stmt.getColumn ("issue_name").getString (),
                                    stmt.getColumn ("project_name").getString (), stmt.getColumn ("assigned_to").getString (),
                                    (bool) stmt.getColumn ("is_closed").getInt ()};
                issues.emplace_back (std::move (issue));
            }
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return issues;
}

bool Sqlite3Driver::DeleteRedMineIssue (int issue_id) const
{
    try
    {
        Statement stmt {d->db, "delete from redmine_issues where issue_id=?"};
        stmt.bind (1, issue_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::UpdateRedMineIssue (RedMineIssue &issue) const
{
    try
    {
        Statement stmt {d->db,
                        "insert or replace into redmine_issues(issue_id, issue_name, project_name, assigned_to, is_closed) values (?,?,?,?,?);"};
        stmt.bind (1, issue.issue_id);
        stmt.bind (2, issue.issue_name);
        stmt.bind (3, issue.project_name);
        stmt.bind (4, issue.assigned_to);
        stmt.bind (5, (int) issue.is_closed);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

WorkItem Sqlite3Driver::NewWorkItem (double hour, const std::string &comment, const std::string &note, const std::string &create_date, int32_t priority) const
{
    WorkItem item;
    item.work_id     = 0;
    item.issue_id    = 0;
    item.act_id      = 0;
    item.is_uploaded = false;
    item.note        = note;
    item.comment     = comment;
    item.hour        = hour;
    item.create_date = create_date;
    item.priority    = priority;

    try
    {
        Statement stmt (d->db, "insert into work_items(hour, comment, note, create_date, priority) values(?,?,?,?,?) returning work_id;");
        stmt.bind (1, hour);
        stmt.bind (2, comment);
        stmt.bind (3, note);
        stmt.bind (4, create_date);
        stmt.bind (5, priority);
        if (stmt.executeStep ())
        {
            item.work_id = stmt.getColumn (0);
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return item;
}

bool Sqlite3Driver::SetWorkItemActivity (int work_id, int act_id) const
{
    try
    {
        Statement stmt {d->db, "update work_items set act_id=? where work_id=?"};
        stmt.bind (1, act_id);
        stmt.bind (2, work_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::SetWorkItemIssue (int work_id, int issue_id) const
{
    try
    {
        Statement stmt {d->db, "update work_items set issue_id=? where work_id=?"};
        stmt.bind (1, issue_id);
        stmt.bind (2, work_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::SetWorkItemUploaded (int work_id) const
{
    try
    {
        Statement stmt {d->db, "update work_items set is_uploaded=1 where work_id=?"};
        stmt.bind (1, work_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::UpdateWorkItem (WorkItem &item) const
{
    try
    {
        Statement stmt {d->db, "update work_items set create_date=?,hour=?,comment=?,note=?,priority=? where work_id=?"};
        stmt.bind (1, item.create_date);
        stmt.bind (2, item.hour);
        stmt.bind (3, item.comment);
        stmt.bind (4, item.note);
        stmt.bind (5, item.priority);
        stmt.bind (6, item.work_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::DeleteWorkItem (int work_id) const
{
    try
    {
        Statement stmt {d->db, "delete from work_items where work_id=?;"};
        stmt.bind (1, work_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::AddItemTag (int work_id, int tag_id) const
{
    try
    {
        Statement stmt {d->db, "insert into work_item_tags(work_id, tag_id) values (?,?);"};
        stmt.bind (1, work_id);
        stmt.bind (2, tag_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::DeleteItemTag (int work_id, int tag_id) const
{
    try
    {
        Statement stmt {d->db, "delete from work_item_tags where work_id=? and tag_id=?;"};
        stmt.bind (1, work_id);
        stmt.bind (2, tag_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::ClearItemTags (int work_id) const
{
    try
    {
        Statement stmt {d->db, "delete from work_item_tags where work_id=?;"};
        stmt.bind (1, work_id);
        stmt.executeStep ();
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

std::vector<std::string> Sqlite3Driver::GetYearList () const
{
    std::vector<std::string> result;
    try
    {
        Statement stmt {d->db, "select strftime('%Y', create_date) as year from work_items group by year order by year;"};
        while (stmt.executeStep ())
            result.emplace_back (stmt.getColumn (0).getString ());
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return result;
}

std::vector<std::string> Sqlite3Driver::GetMonthList (const std::string &year) const
{
    // select strftime('%m', create_date) as month from work_items where strftime('%Y', create_date)='2021' group by month;
    std::vector<std::string> result;
    try
    {
        Statement stmt {
                d->db,
                "select strftime('%m', create_date) as month from work_items where strftime('%Y', create_date)=? group by month order by month;"};
        stmt.bind (1, year);
        while (stmt.executeStep ())
            result.emplace_back (stmt.getColumn (0).getString ());
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return result;
}

std::vector<std::string> Sqlite3Driver::GetDayList (const std::string &year_and_month) const
{
    std::vector<std::string> result;
    try
    {
        Statement stmt {
                d->db, "select strftime('%d', create_date) as day from work_items where strftime('%Y-%m', create_date)=? group by day order by day;"};
        stmt.bind (1, year_and_month);
        while (stmt.executeStep ())
            result.emplace_back (stmt.getColumn (0).getString ());
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return result;
}

std::vector<WorkItem> Sqlite3Driver::GetDailyItems (const std::string &date) const
{
    std::vector<WorkItem> items;

    try
    {
        Statement count {d->db, "select count(*) from work_items where create_date=?;"};
        count.bind (1, date);
        count.executeStep ();
        const int total = count.getColumn (0);
        Statement stmt {d->db, "select * from work_items where create_date=? order by priority asc;"};
        stmt.bind (1, date);
        if (total > 0)
        {
            items.reserve (total);
            while (stmt.executeStep ())
            {
                WorkItem issue {stmt.getColumn ("work_id").getInt (),        stmt.getColumn ("hour").getDouble (),
                                stmt.getColumn ("comment").getString (),     stmt.getColumn ("note").getString (),
                                stmt.getColumn ("create_date").getString (), stmt.getColumn ("act_id").getInt (),
                                stmt.getColumn ("issue_id").getInt (),       stmt.getColumn ("priority").getInt (),
                                (bool) stmt.getColumn ("is_uploaded").getInt ()};
                items.emplace_back (std::move (issue));
            }
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return items;
}

std::vector<WorkItem> Sqlite3Driver::GetItemsByTagAndDate (const std::string &date_start, const std::string &date_end, int tag_id_l1,
                                                           int tag_id_l2) const
{
    std::vector<WorkItem> items;
    try
    {
        if (tag_id_l2 == 0)
        {
            Statement stmt {d->db, R"sql(
            select work_items.* from (
  work_items inner join work_item_tags on work_items.work_id = work_item_tags.work_id
) where work_item_tags.tag_id=? and work_items.create_date between ? and ? order by work_items.create_date, work_items.work_id;
            )sql"};
            stmt.bind (1, tag_id_l1);
            stmt.bind (2, date_start);
            stmt.bind (3, date_end);
            while (stmt.executeStep ())
            {
                WorkItem issue {stmt.getColumn ("work_id").getInt (),        stmt.getColumn ("hour").getDouble (),
                                stmt.getColumn ("comment").getString (),     stmt.getColumn ("note").getString (),
                                stmt.getColumn ("create_date").getString (), stmt.getColumn ("act_id").getInt (),
                                stmt.getColumn ("issue_id").getInt (),       stmt.getColumn ("priority").getInt (),
                                (bool) stmt.getColumn ("is_uploaded").getInt ()};
                items.emplace_back (std::move (issue));
            }
        }
        else
        {
            Statement stmt {d->db, R"sql(
        select work_items.* from work_items inner join (select work_item_tags.work_id from (select work_id
           from work_item_tags
           where tag_id = ?) as t0
        inner join work_item_tags on t0.work_id = work_item_tags.work_id
        and work_item_tags.tag_id = ?) as t1 on work_items.work_id = t1.work_id where create_date between ? and ? order by work_items.create_date, work_items.work_id;
            )sql"};
            stmt.bind (1, tag_id_l1);
            stmt.bind (2, tag_id_l2);
            stmt.bind (3, date_start);
            stmt.bind (4, date_end);
            while (stmt.executeStep ())
            {
                WorkItem issue {stmt.getColumn ("work_id").getInt (),        stmt.getColumn ("hour").getDouble (),
                                stmt.getColumn ("comment").getString (),     stmt.getColumn ("note").getString (),
                                stmt.getColumn ("create_date").getString (), stmt.getColumn ("act_id").getInt (),
                                stmt.getColumn ("issue_id").getInt (),       stmt.getColumn ("priority").getInt (),
                                (bool) stmt.getColumn ("is_uploaded").getInt ()};
                items.emplace_back (std::move (issue));
            }
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return items;
}

StatisticsResult Sqlite3Driver::GetStatisticsResult (const std::string &date_start, const std::string &date_end) const
{
    StatisticsResult result {.date_start = date_start, .date_end = date_end, .total_time = 0.0};

    try
    {
        // total hours
        {
            Statement stmt {d->db, "select sum(hour) as total from work_items where create_date BETWEEN ? AND ?;"};
            stmt.bind (1, date_start);
            stmt.bind (2, date_end);
            if (stmt.executeStep ())
            {
                result.total_time = stmt.getColumn ("total").getDouble ();
            }
        }

        // primary tag times
        {
            Statement stmt {d->db, R"sql(
SELECT
    tags.tag_id, sum(hour) as total_hours, tag_name
FROM
    ((work_item_tags inner join
        (SELECT work_id,hour from work_items where create_date BETWEEN ? AND ?) as t1 on work_item_tags.work_id=t1.work_id) as t2
        inner join tags on tags.tag_id=t2.tag_id and tags.tag_level=0) group by tags.tag_id;
                )sql"};
            stmt.bind (1, date_start);
            stmt.bind (2, date_end);
            while (stmt.executeStep ())
            {
                result.tags.emplace_back (stmt.getColumn (0).getInt (), stmt.getColumn ("tag_name").getString (),
                                          stmt.getColumn ("total_hours").getDouble ());
            }
        }

        // sub tag times
        {
            for (auto &x: result.tags)
            {
                Statement stmt {d->db, R"sql(
select tags.tag_id,

       sum(hour) as total_hours,

       tag_name

  from (((
          (select work_id
           from work_item_tags
           where tag_id = ?) as t0
        inner join work_item_tags on t0.work_id = work_item_tags.work_id
        and work_item_tags.tag_id != ?)
       inner join
         (select work_id,
                 hour
          from work_items
          where create_date between ? and ?) as t1 on t0.work_id = t1.work_id) as t2
      inner join tags on tags.tag_id = t2.tag_id and tags.tag_level != 0)

 group by tags.tag_id;
)sql"};
                stmt.bind (1, x.tag_id);
                stmt.bind (2, x.tag_id);
                stmt.bind (3, date_start);
                stmt.bind (4, date_end);
                while (stmt.executeStep ())
                {
                    x.sub_tags.emplace_back (stmt.getColumn (0).getInt (), stmt.getColumn ("tag_name").getString (),
                                             stmt.getColumn ("total_hours").getDouble ());
                }
            }
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return result;
}

StatisticsResult Sqlite3Driver::GetStatisticsResult () const
{
    StatisticsResult result {.total_time = 0.0};

    try
    {
        // get begin time and end time
        {
            Statement range {d->db,
                             "select t1.create_date as date_begin, t2.create_date as date_end from (select create_date from work_items ORDER by "
                             "create_date asc limit 1) as t1, (select create_date from work_items ORDER by create_date desc limit 1) as t2;"};
            if (range.executeStep ())
            {
                result.date_start = range.getColumn ("date_begin").getString ();
                result.date_end   = range.getColumn ("date_end").getString ();
            }
        }

        if (!result.date_start.empty () && !result.date_end.empty ())
        {
            result = GetStatisticsResult (result.date_start, result.date_end);
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return result;
}

StatisticsResult Sqlite3Driver::GetStatisticsResult(const std::string &date_start, const std::string &date_end, int tag_id) const 
{
    StatisticsResult result {.total_time = 0.0};

    return result;
}

void Sqlite3Driver::InitConnection ()
{
    // nothing
}

bool Sqlite3Driver::IsConfigured ()
{
    return true;  // no configure need
}

std::vector<Tag> Sqlite3Driver::AllWorkItemTags (int work_id)
{
    std::vector<Tag> result;

    try
    {
        Statement stmt {d->db,
                        "select tags.* from work_item_tags inner join tags on work_item_tags.tag_id=tags.tag_id where work_item_tags.work_id=? order "
                        "by tag_level asc, tag_id asc"};
        stmt.bind (1, work_id);
        while (stmt.executeStep ())
        {
            Tag tag {stmt.getColumn ("tag_id").getInt (), stmt.getColumn ("tag_name").getString (), stmt.getColumn ("tag_color").getUInt (),
                     stmt.getColumn ("tag_level").getUInt ()};
            result.emplace_back (std::move (tag));
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }

    return result;
}

WorkTask Sqlite3Driver::NewTask (std::string task_desc, const std::string &create_date, uint32_t color)
{
    WorkTask task {.task_id = -1, .task_desc = task_desc, .create_date = create_date};
    try
    {
        Statement stmt {d->db, "insert into work_tasks(task_desc, create_date, display_color_rgba) values(?,?,?) returning task_id;"};
        stmt.bind (1, task_desc);
        stmt.bind (2, create_date);
        stmt.bind (3, color);
        if (stmt.executeStep ())
            task.task_id = stmt.getColumn (0).getInt ();
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return task;
}

bool Sqlite3Driver::UpdateTask (const WorkTask &task)
{
    try
    {
        std::string tag_list;
        tag_list = std::accumulate (begin (task.tags), end (task.tags), std::string {}, [] (auto v, auto x) {
            if (!v.empty ())
                v.push_back (',');
            return v + std::to_string (x);
        });
        Statement stmt {d->db, "update work_tasks set act_id=?, issue_id=?, tags=?, display_color_rgba=?, last_time_entry=? where task_id=?;"};
        stmt.bind (1, task.redmine_act_id);
        stmt.bind (2, task.redmine_issue_id);
        stmt.bind (3, tag_list);
        stmt.bind (4, task.display_color_rgba);
        stmt.bind (5, task.last_time_entry);
        stmt.bind (6, task.task_id);
        if (stmt.executeStep ())
            return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::DeleteTask (const WorkTask &task)
{
    try
    {
        Statement stmt {d->db, "delete from work_tasks where task_id=?;"};
        stmt.bind (1, task.task_id);
        if (stmt.executeStep ())
            return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

bool Sqlite3Driver::SetTaskStatus (WorkTask &task, TaskStatus status)
{
    try
    {
        Statement stmt {d->db, "update work_tasks set status=? where task_id=?;"};
        stmt.bind (1, (int) status);
        stmt.bind (2, (int) task.task_id);
        stmt.exec();
        task.status = status;
        return true;
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}

static std::vector<int> to_ints(const std::string &in)
{
    std::vector<int> ret;
    size_t s = 0, i = 0;
    while ((i = in.find(',', s)) != std::string::npos)
    {
        try
        {
            ret.emplace_back(std::stoi(in.substr(s, i - s)));
            s = i + 1;
        } catch (...)
        {
            break;
        }
    }
    if (s < in.length())
    {
        try
        {
            ret.emplace_back(std::stoi(in.substr(s, i - s)));
            s = i + 1;
        } catch (...)
        {}
    }
    return ret;
}

std::vector<WorkTask> Sqlite3Driver::GetTasks () const
{
    std::vector<WorkTask> result;
    try
    {
        Statement count{ d->db, "select count(*) from work_tasks;" };
        count.executeStep();
        const int total = count.getColumn(0);
        Statement stmt{ d->db,
                        "select * from work_tasks order by status asc;" };
        if (total > 0)
        {
            result.reserve(total);
            while (stmt.executeStep())
            {
                WorkTask task{};
                task.task_id = stmt.getColumn(0).getInt();
                task.task_desc = stmt.getColumn(1).getString();
                task.create_date = stmt.getColumn(2).getString();
                task.redmine_issue_id = GetValue(stmt.getColumn(3), -1);
                task.redmine_act_id = GetValue(stmt.getColumn(4), -1);
                task.status = static_cast<TaskStatus> (stmt.getColumn (5).getInt ());
                task.tags = to_ints(stmt.getColumn(6).getString());
                task.display_color_rgba = stmt.getColumn(7).getUInt();
                task.last_time_entry = stmt.getColumn(8).getInt();
                result.emplace_back(std::move(task));
            }
        }
    }
    catch (Exception& ex)
    {
        std::cerr << ex.what() << std::endl;
    }
    return result;
}

WorkTask Sqlite3Driver::GetCurrentTask () const
{
    WorkTask result{ .task_id = -1, .last_time_entry = -1 };
    try
    {
        Statement stmt{ d->db,
                        "select * from work_tasks where status=?;" };
        stmt.bind(1, (int)eInProgress);
        if (stmt.executeStep ())
        {
            result.task_id = stmt.getColumn(0).getInt();
            result.task_desc = stmt.getColumn(1).getString();
            result.create_date = stmt.getColumn(2).getString();
            result.redmine_issue_id = stmt.getColumn(3).getInt();
            result.redmine_act_id = stmt.getColumn(4).getInt();
            result.status = static_cast<TaskStatus> (stmt.getColumn(5).getInt());
            result.tags = to_ints(stmt.getColumn(6).getString());
            result.display_color_rgba = stmt.getColumn(7).getUInt();
            result.last_time_entry = stmt.getColumn(8).getInt();
        }
    }
    catch (Exception& ex)
    {
        std::cerr << ex.what() << std::endl;
    }
    return result;
}

void Sqlite3Driver::ClearActiveTask () const
{
    try
    {
        Statement stmt{ d->db, "update work_tasks set status=? where status=?;" };
        stmt.bind(1, (int)ePaused);
        stmt.bind(2, (int)eInProgress);
        stmt.executeStep();
    }
    catch (Exception& ex)
    {
        std::cerr << ex.what() << std::endl;
    }
}

TimeEntry Sqlite3Driver::NewTimeEntry (int32_t task_id, const std::string &start_date_time)
{
    TimeEntry entry{.entry_id = -1, .task_id = task_id, .begin_time = start_date_time, .duration = 0, .exported = 0};
    try
    {
        Statement stmt {d->db,
                        "insert into time_entries(task_id, begin_time) values(?,?) returning entry_id;"};
        if (task_id > -1)
            stmt.bind(1, task_id);
        else
            stmt.bind(1);
        stmt.bind(2, start_date_time);
        if (stmt.executeStep()) {
            entry.entry_id = stmt.getColumn (0).getInt();
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return entry;
}

bool Sqlite3Driver::UpdateTimeEntry (TimeEntry &entry, const std::string &end_time, int32_t duration)
{
    try
    {
        Statement stmt {d->db,
                        "update time_entries set end_time=?, duration=? where entry_id=?;"};
        stmt.bind (1, end_time);
        stmt.bind (2, duration);
        stmt.bind (3, entry.entry_id);
        if (stmt.executeStep()) {
            entry.duration = duration;
            return true;
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return false;
}
void Sqlite3Driver::MarkTimeEntryAsExported (TimeEntry &entry) const {
    try
    {
        Statement stmt {d->db,
                        "update time_entries set exported=1 where entry_id=?;"};
        stmt.bind (1, entry.entry_id);
        if (stmt.executeStep()) {
            entry.exported = 1;
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
}
std::vector<TimeEntry> Sqlite3Driver::GetTimeEntries (int32_t task_id, const std::string &date_beg, const std::string &date_end)
{
    std::vector<TimeEntry> result;
    try
    {
        std::string datetime_begin = date_beg + " 00:00:00";
        std::string datetime_end = date_beg + " 23:59:59";
        Statement stmt {d->db,
                        "select * from time_entries where task_id=$tid and begin_time between $beg and $end;"};
        stmt.bind("$tid", task_id);
        stmt.bind("$beg", datetime_begin);
        stmt.bind("$end", datetime_end);
        while(stmt.executeStep()) {
            TimeEntry entry{};
            entry.entry_id = stmt.getColumn(0).getInt();
            entry.task_id = GetValue (stmt.getColumn (1), -1);
            entry.begin_time = stmt.getColumn(2).getString();
            entry.end_time = GetValue (stmt.getColumn (3), std::string{""});
            entry.duration = GetValue(stmt.getColumn(4), 0);
            entry.exported = stmt.getColumn(5).getInt();
            result.emplace_back (std::move(entry));
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return result;
}

std::vector<TimeEntry> Sqlite3Driver::GetTimeEntries (const std::string &date_beg, const std::string &date_end) const
{
    std::vector<TimeEntry> result;
    try
    {
        std::string datetime_begin = date_beg + " 00:00:00";
        std::string datetime_end = date_beg + " 23:59:59";
        Statement stmt {d->db,
                        "select * from time_entries where begin_time between $beg and $end and end_time between $beg and $end;"};
        stmt.bind("$beg", datetime_begin);
        stmt.bind("$end", datetime_end);
        while(stmt.executeStep()) {
            TimeEntry entry{};
            entry.entry_id = stmt.getColumn(0).getInt();
            entry.task_id = GetValue (stmt.getColumn (1), -1);
            entry.begin_time = stmt.getColumn(2).getString();
            entry.end_time = GetValue (stmt.getColumn (3), std::string{""});
            entry.duration = GetValue(stmt.getColumn(4), 0);
            entry.exported = stmt.getColumn(5).getInt();
            result.emplace_back (std::move(entry));
        }
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return result;
}

TimeEntry Sqlite3Driver::GetCurrentTimeEntry (int32_t entry_id) const
{
    TimeEntry result{ .entry_id = -1 };
    try
    {
        if (entry_id > -1)
        {
            Statement stmt{ d->db,
                        "select * from time_entries where entry_id=?;" };
            stmt.bind(1, entry_id);
            if (stmt.executeStep ())
            {
                result.entry_id = stmt.getColumn(0).getInt();
                result.task_id = GetValue (stmt.getColumn (1), -1);
                result.begin_time = stmt.getColumn(2).getString();
                result.end_time = GetValue (stmt.getColumn (3), std::string{""});
                result.duration = GetValue(stmt.getColumn(4), 0);
                result.exported = stmt.getColumn(5).getInt();
            }
        }
        else
        {
            Statement stmt{ d->db,
                        "select * from time_entries where end_time is null order by entry_id desc limit 1;" };
            if (stmt.executeStep())
            {
                result.entry_id = stmt.getColumn(0).getInt();
                result.task_id = GetValue (stmt.getColumn (1), -1);
                result.begin_time = stmt.getColumn(2).getString();
                result.end_time = GetValue (stmt.getColumn (3), std::string{""});
                result.duration = GetValue(stmt.getColumn(4), 0);
                result.exported = stmt.getColumn(5).getInt();
            }
        }
    }
    catch (Exception& ex)
    {
        std::cerr << ex.what() << std::endl;
    }
    return result;
}

int32_t Sqlite3Driver::GetDataVersion () const
{
    int32_t result = MAKE_VERSION_CODE (1, 0, 0);
    try
    {
        Statement stmt {d->db, "select version_code from _version_ order by version_code desc limit 1;"};
        if (stmt.executeStep ())
            result = stmt.getColumn (0).getInt ();
    }
    catch (Exception &ex)
    {
        std::cerr << ex.what () << std::endl;
    }
    return result;
}

void Sqlite3Driver::InitPreparedStatements () const
{
    // nothing
}

void Sqlite3Driver::MigrateTables (int32_t old_version) const
{
    int32_t current_version = old_version;
    while (current_version != DATA_VERSION)
    {
        auto const &info = GetDatabaseUpgradeInfo (current_version);

        // execute statements
        for (auto &x: info.statements)
        {
            if (x)
            {
                try
                {
                    Statement stmt {d->db, x};
                    stmt.exec ();
                }
                catch (Exception &ex)
                {
                    perror (ex.what ());

                    abort();
                }
            }
        }
        try
        {
            Statement stmt {d->db, "insert into _version_(version_code) values (?);"};
            stmt.bind (1, info.version_to);
            stmt.exec ();
        }
        catch (Exception &ex)
        {
            perror (ex.what ());
            abort ();
        }
        current_version = info.version_to;
    }

    current_version = GetDataVersion ();
    assert (current_version == DATA_VERSION);
}

static ISqlDriver *CreateSqlite3Driver ()
{
    return new Sqlite3Driver {};
}

const DriverFactory *GetSqlite3Driver ()
{
    static DriverFactory factory {"SQLITE3", CreateSqlite3Driver};
    return &factory;
}
